package com.css.bjdt_core.reportFill.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

import javax.servlet.http.HttpSession;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.nutz.dao.Cnd;
import org.nutz.dao.Condition;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.css.bjdt_core.annual.entity.RptAnnual;
import com.css.bjdt_core.cell.entity.Cell;
import com.css.bjdt_core.cell.entity.Cell.CellType;
import com.css.bjdt_core.cell.entity.CellData;
import com.css.bjdt_core.cell.service.CellDataService;
import com.css.bjdt_core.cell.service.CellService;
import com.css.bjdt_core.dnDzz.entity.DnDzz;
import com.css.bjdt_core.dnDzz.service.DnDzzService;
import com.css.bjdt_core.executive.report.ReportCache;
import com.css.bjdt_core.executive.report.ReportRequest;
import com.css.bjdt_core.explain.service.ReportTemplateExplainService;
import com.css.bjdt_core.report.entity.RptAreaTemporary;
import com.css.bjdt_core.report.entity.RptTemplate;
import com.css.bjdt_core.report.entity.ViewRptGroupTemplate;
import com.css.bjdt_core.report.service.ReportTemplateManageService;
import com.css.bjdt_core.reportExplainFill.service.RptCellExplainSummaryNewService;
import com.css.bjdt_core.reportExplainFill.service.RptCellExplainSummaryService;
import com.css.bjdt_core.reportExplainFill.service.RptExplainCellFillService;
import com.css.bjdt_core.reportFill.entity.CellDataValue;
import com.css.bjdt_core.reportFill.entity.CellFormulaEntity;
import com.css.bjdt_core.reportFill.entity.ViewUnitTemplate;
import com.css.bjdt_core.reportPrepare.entity.RptBatch;
import com.css.bjdt_core.reportPrepare.entity.RptBatchTemplateRel;
import com.css.bjdt_core.reportPrepare.entity.RptUnit;
import com.css.bjdt_core.reportPrepare.service.RptDealWithService;
import com.css.bjdt_core.reportPrepare.service.RptUnitService;
import com.css.bjdt_core.summary.service.ReportSummaryNewService;
import com.css.bjdt_core.summary.service.ReportSummaryService;
import com.css.bjdt_core.util.CommonUtil;
import com.css.bjdt_core.util.PageQuerys;
import com.css.bjdt_core.util.ReportConstant;
import com.css.bjdt_core.util.TimeUtil;
import com.css.bjdt_core.validation.entity.ValidationInfo;
import com.css.bjdt_core.validation.service.ValidationInfoService;

//报表填报用到的方法
@Service
public class ReportFillService {
	@Autowired
	private Dao dao;

	@Autowired
	private RptDealWithService rptDealWithService;

	@Autowired
	private RptUnitService rptUnitService;

	@Autowired
	private ReportSummaryService reportSummaryService;

	@Autowired
	private RptCellExplainSummaryService rptCellExplainSummaryService;

	@Autowired
	private ReportSummaryNewService reportSummaryNewService;

	@Autowired
	private RptCellExplainSummaryNewService rptCellExplainSummaryNewService;

	@Autowired
	private RptExplainCellFillService rptExplainCellFillService;

	@Autowired
	private CellDataService cellDataService;

	@Autowired
	private CellService cellService;

	@Autowired
	private ReportTemplateManageService reportTemplateManageService;

	@Autowired
	private ReportTemplateExplainService reportTemplateExplainService;

	@Autowired
	private ValidationInfoService validationInfoService;

	@Autowired
	private DnDzzService dnDzzService;

	private ReportCache cache = ReportCache.getInstance();

	/**
	 * 分页查询该组下的所有子报表(邱实)
	 * @param cnd
	 * @return
	 */
	public Map queryTemplatePage(Condition cnd, int pageNum, int pageSize) {
		Map result = PageQuerys.ligerGridQuery(dao, ViewRptGroupTemplate.class, cnd, pageNum, pageSize);
		return result;
	}

	/**
	 * 分页查询该组下的所有子报表(高杨)
	 * @param cnd
	 * @return
	 */
	public Map queryTemplatePageGy(Condition cnd, int pageNum, int pageSize, String dzzId, String annualId) {
		Map result = PageQuerys.ligerGridQuery(dao, ViewUnitTemplate.class, cnd, pageNum, pageSize);
		List<ViewUnitTemplate> viewUnitTemplateList = (List<ViewUnitTemplate>) result.get("Rows");
		Map<String, Map<Integer, Integer>> validationCounts = validationInfoService.selectValidationCount(annualId,
				dzzId);
		for (ViewUnitTemplate viewUnitTemplate : viewUnitTemplateList) {
			Map<Integer, Integer> counts = validationCounts.get(viewUnitTemplate.getTemplateId());
			viewUnitTemplate.setBiaoneiCount((counts != null && counts.containsKey(1) ? counts.get(1) : 0) + "");
			viewUnitTemplate.setBiaojianCount((counts != null && counts.containsKey(2) ? counts.get(2) : 0) + "");
			viewUnitTemplate.setShuomingCount((counts != null && counts.containsKey(3) ? counts.get(3) : 0) + "");
		}
		result.remove("Rows");
		result.put("Rows", viewUnitTemplateList);
		return result;
	}

	//得到所有的报表版本
	public List<RptAnnual> queryAllAnnual() {
		return dao.query(RptAnnual.class, Cnd.orderBy().desc("ORDERID"));
	}

	//通过单位和报表的版本获得填报信息
	public RptUnit getFillInfoByCond(String annualId, String unitId) {
		return dao.fetch(RptUnit.class, Cnd.where("ANNUALID", "=", annualId).and("UNITID", "=", unitId));
	}

	//通过报表的ID和报表版本的ID，获得该张报表的数据区域
	public RptAreaTemporary getARptAreaTemporary(String templateId, String annualId) {
		return dao.fetch(RptAreaTemporary.class,
				Cnd.where("TEMPLATEID", "=", templateId).and("ANNUALID", "=", annualId));
	}

	//非汇总数据读取
	public List<CellDataValue> getAllCellDataValueNew(String annualId, String templateId, String batchId,
			String dzzId) {
		//去掉老的方法，留新的方法，等系统稳定了，再把老方法都删掉，心里没底，新方法有没有BUG
		Map<String, CellDataValue> cellDataValueMap = new HashMap<String, CellDataValue>();
		List<Cell> cellList = dao.query(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId));
		RptUnit rptUnit = rptUnitService.queryByDzzIdAndAnnualId(dzzId, annualId);
		RptBatch rptBatch = rptDealWithService.queryBatch(batchId);
		//查询党组织信息
		DnDzz dnDzz = dnDzzService.getDnDzzDataByDzzId(dzzId);
		for (Cell cell : cellList) {
			String mapKey = cell.getAbsRowNum() + "--" + cell.getAbsColNum();
			CellDataValue cellDataValue = new CellDataValue();
			cellDataValue.setBatchId(batchId);
			cellDataValue.setAbsRowNum(cell.getAbsRowNum() + "");
			cellDataValue.setAbsColNum(cell.getAbsColNum() + "");
			cellDataValue.setRelRowNum(cell.getRelRowNum() + "");
			cellDataValue.setRelColNum(cell.getRelColNum() + "");
			cellDataValue.setDzzId(dzzId);
			cellDataValue.setTemplateId(templateId);
			cellDataValue.setCellType(cell.getCellType());
			cellDataValue.setIsHaveComment(cell.getIsComment());
			//判断该指标是不是为空，如果为空就给它赋值--，如果他是预设类指标，就直接给他赋相对的预设类指标
			if (cell.getCellType().equals(CellType.cellNull.getKey())) {
				cellDataValue.setCellValue("--");
			} else if (cell.getCellType().equals(CellType.quote.getKey())) {
				switch (cell.getQuoteType()) {
				case "1":
					//北大的历史数据迁移过来之后，没有单位的基本信息，所以这边要判断下是否存在单位信息
					if (null == rptUnit || null == rptUnit.getFullName()) {
						//如果不存在，去库里面查一下，拿党组织的信息代替填报单位的信息
						cellDataValue.setCellValue("填报单位：" + dnDzz.getDzzjc());
					} else {
						cellDataValue.setCellValue("填报单位：" + rptUnit.getFullName());
					}
					break;
				case "2":
					cellDataValue.setCellValue("截止时间：" + TimeUtil.dateFormatString(rptBatch.getEndTime()));
					break;
				case "3":
					cellDataValue.setCellValue("开始时间：" + TimeUtil.dateFormatString(rptBatch.getStartTime()));
					break;
				case "4":
					cellDataValue.setCellValue("起止时间：" + TimeUtil.dateFormatString(rptBatch.getStartTime()) + "至"
							+ TimeUtil.dateFormatString(rptBatch.getEndTime()));
					break;
				case "5":
					cellDataValue.setCellValue(rptUnit.getRelMan());
					break;
				default:
					break;
				}
			} else if (cell.getCellType().equals(CellType.input.getKey())) {
				cellDataValue.setCellValue("0");
			} else {
			}
			cellDataValueMap.put(mapKey, cellDataValue);
		}

		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		Sql strSql = Sqls
				.create("select CELL_DATA_ID,CELL_VALUE,BATCH_ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID,TEMPLATE_ID,HISTORY_CELL_VALUE,SUM_STATUS,BATCH_ID from "
						+ tableName + " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId order by CELL_VALUE");
		strSql.params().set("dzzId", dzzId).set("templateId", templateId);
		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<CellDataValue> cellDataValueList = new ArrayList<CellDataValue>();
				while (rs.next()) {
					CellDataValue cellDataValue = new CellDataValue();
					cellDataValue.setAbsColNum(rs.getString("abs_col_num"));
					cellDataValue.setAbsRowNum(rs.getString("abs_row_num"));
					cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
					cellDataValue.setCellDataId(rs.getString("CELL_DATA_ID"));
					cellDataValue.setHistoryCellValue(rs.getString("HISTORY_CELL_VALUE"));
					cellDataValue.setSumStatus(rs.getString("SUM_STATUS"));
					cellDataValue.setBatchId(rs.getString("BATCH_ID"));
					cellDataValueList.add(cellDataValue);
				}
				return cellDataValueList;
			}
		});
		dao.execute(strSql);
		List<CellDataValue> cdv = strSql.getList(CellDataValue.class);
		if (cdv != null && cdv.size() != 0) {
			for (CellDataValue cellDataValue : cdv) {
				String key = cellDataValue.getAbsRowNum() + "--" + cellDataValue.getAbsColNum();
				CellDataValue cellDataValueNew = cellDataValueMap.get(key);
				if (null != cellDataValueNew) {
					cellDataValueNew.setCellValue(cellDataValue.getCellValue());
					cellDataValueNew.setCellDataId(cellDataValue.getCellDataId());
					cellDataValueNew.setHistoryCellValue(cellDataValue.getHistoryCellValue());
					cellDataValueMap.put(key, cellDataValueNew);
				}
			}
		}
		//最后把MAP转换成LIST，返回
		List<CellDataValue> cellDataValueListJsp = new ArrayList<CellDataValue>();
		for (String key : cellDataValueMap.keySet()) {
			CellDataValue cellDataValueNew = cellDataValueMap.get(key);
			cellDataValueListJsp.add(cellDataValueNew);
		}
		return cellDataValueListJsp;
	}

	//汇总数据读取
	public List<CellDataValue> getAllCellDataValueNew(String annualId, String templateId, final String batchId,
			String dzzId, String summary) {
		Map<String, CellDataValue> cellDataValueMap = new HashMap<String, CellDataValue>();
		List<Cell> cellList = dao.query(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId));
		RptUnit rptUnit = rptUnitService.queryByDzzIdAndAnnualId(dzzId, annualId);
		RptBatch rptBatch = rptDealWithService.queryBatch(batchId);
		//查询党组织信息
		DnDzz dnDzz = dnDzzService.getDnDzzDataByDzzId(dzzId);
		for (Cell cell : cellList) {
			String mapKey = cell.getAbsRowNum() + "--" + cell.getAbsColNum();
			CellDataValue cellDataValue = new CellDataValue();
			cellDataValue.setBatchId(batchId);
			cellDataValue.setAbsRowNum(cell.getAbsRowNum() + "");
			cellDataValue.setAbsColNum(cell.getAbsColNum() + "");
			cellDataValue.setRelRowNum(cell.getRelRowNum() + "");
			cellDataValue.setRelColNum(cell.getRelColNum() + "");
			cellDataValue.setDzzId(dzzId);
			cellDataValue.setTemplateId(templateId);
			cellDataValue.setCellType(cell.getCellType());
			cellDataValue.setIsHaveComment(cell.getIsComment());
			//判断该指标是不是为空，如果为空就给它赋值--，如果他是预设类指标，就直接给他赋相对的预设类指标
			if (cell.getCellType().equals(CellType.cellNull.getKey())) {
				cellDataValue.setCellValue("--");
			} else if (cell.getCellType().equals(CellType.quote.getKey())) {
				switch (cell.getQuoteType()) {
				case "1":
					//北大的历史数据迁移过来之后，没有单位的基本信息，所以这边要判断下是否存在单位信息
					if (null == rptUnit || null == rptUnit.getFullName()) {
						//如果不存在，去库里面查一下，拿党组织的信息代替填报单位的信息
						cellDataValue.setCellValue("填报单位：" + dnDzz.getDzzjc());
					} else {
						cellDataValue.setCellValue("填报单位：" + rptUnit.getFullName());
					}
					break;
				case "2":
					cellDataValue.setCellValue("截止时间：" + TimeUtil.dateFormatString(rptBatch.getEndTime()));
					break;
				case "3":
					cellDataValue.setCellValue("开始时间：" + TimeUtil.dateFormatString(rptBatch.getStartTime()));
					break;
				case "4":
					cellDataValue.setCellValue("起止时间：" + TimeUtil.dateFormatString(rptBatch.getStartTime()) + " 至   "
							+ TimeUtil.dateFormatString(rptBatch.getEndTime()));
					break;
				case "5":
					cellDataValue.setCellValue(rptUnit.getRelMan());
					break;
				default:
					break;
				}
			} else if (cell.getCellType().equals(CellType.input.getKey())) {
				cellDataValue.setCellValue("0");
			} else {
			}
			cellDataValueMap.put(mapKey, cellDataValue);
		}

		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		Sql strSql = Sqls
				.create("select CELL_DATA_ID,CELL_VALUE,BATCH_ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID,TEMPLATE_ID,HISTORY_CELL_VALUE,SUM_STATUS,BATCH_ID from "
						+ tableName
						+ " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId and SUM_STATUS = @summary order by CELL_VALUE");
		strSql.params().set("dzzId", dzzId).set("templateId", templateId).set("summary", summary);
		strSql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<CellDataValue> cellDataValueList = new ArrayList<CellDataValue>();
				while (rs.next()) {
					CellDataValue cellDataValue = new CellDataValue();
					cellDataValue.setAbsColNum(rs.getString("abs_col_num"));
					cellDataValue.setAbsRowNum(rs.getString("abs_row_num"));
					cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
					cellDataValue.setCellDataId(rs.getString("CELL_DATA_ID"));
					cellDataValue.setHistoryCellValue(rs.getString("HISTORY_CELL_VALUE"));
					cellDataValue.setSumStatus(rs.getString("SUM_STATUS"));
					cellDataValue.setBatchId(rs.getString("BATCH_ID"));
					cellDataValue.setSummary("1");
					cellDataValueList.add(cellDataValue);
				}
				return cellDataValueList;
			}
		});
		dao.execute(strSql);
		List<CellDataValue> cdv = strSql.getList(CellDataValue.class);
		if (cdv != null && cdv.size() != 0) {
			for (CellDataValue cellDataValue : cdv) {
				String key = cellDataValue.getAbsRowNum() + "--" + cellDataValue.getAbsColNum();
				CellDataValue cellDataValueNew = cellDataValueMap.get(key);
				if (null == cellDataValueNew) {
					continue;
				}
				cellDataValueNew.setCellValue(cellDataValue.getCellValue());
				cellDataValueNew.setCellDataId(cellDataValue.getCellDataId());
				cellDataValueMap.put(key, cellDataValueNew);
			}
		}
		//最后把MAP转换成LIST，返回
		List<CellDataValue> cellDataValueListJsp = new ArrayList<CellDataValue>();
		for (String key : cellDataValueMap.keySet()) {
			CellDataValue cellDataValueNew = cellDataValueMap.get(key);
			cellDataValueListJsp.add(cellDataValueNew);
		}
		return cellDataValueListJsp;
	}

	//根据报表版本ID和单位ID获得报表实例ID（需要修改）
	public String getTemplateDataId(String templateId, String unitId) {
		RptBatchTemplateRel rptUnitTemplateRel = dao.fetch(RptBatchTemplateRel.class,
				Cnd.where("TEMPLATEID", "=", templateId).and("UNITID", "=", unitId));
		return rptUnitTemplateRel.getId();
	}

	//根据单位ID获得报表实例ID()
	public List<RptTemplate> getTemplateListByUnitId(String unitId, String annualId) {
		Sql sql = Sqls.create(
				"select t1.TEMPLATEID,t1.orderid  from RPT_UNIT_TEMPLATE_R t left join Rpt_Template t1 on(t.templateid=t1.templateid) where t.unitid=@unitId and t.annualid = @annualid order by t1.orderid desc");
		sql.params().set("unitId", unitId);
		sql.params().set("annualid", annualId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<RptTemplate> rptTemplateList = new ArrayList<RptTemplate>();
				while (rs.next()) {
					RptTemplate rptTemplate = new RptTemplate();
					rptTemplate.setTemplateId(rs.getString("TEMPLATEID"));
					rptTemplateList.add(rptTemplate);
				}
				return rptTemplateList;
			}
		});
		dao.execute(sql);
		List<RptTemplate> aList = sql.getList(RptTemplate.class);
		return sql.getList(RptTemplate.class);
	}

	public RptTemplate getARptTemplate(String templateId) {
		return dao.fetch(RptTemplate.class, Cnd.where("TEMPLATEID", "=", templateId));
	}

	//获得单张报表的校核信息
	public List<ValidationInfo> getATemplateValidationInfo(String annualId, String templateId, String unitId) {
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		Sql strSql = Sqls.create("select ID,UNIT_ID,TEMPLATE_ID,ANNUAL_ID,VALIDATION_EXP,VALIDATION_STATE,CELL_ID from "
				+ tableName + " where TEMPLATE_ID = @templateId and UNIT_ID = @unitId");
		strSql.params().set("templateId", templateId).set("unitId", unitId);
		strSql.setCallback(new SqlCallback() {

			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				while (rs.next()) {
					ValidationInfo validationInfo = new ValidationInfo();
					validationInfo.setId(rs.getString("ID"));
				}
				return null;
			}
		});
		return null;
	}

	//获得该单位下的一套套表下的所有需要填报的报表
	public List<ViewUnitTemplate> getAllFillTemplateDataIdByGroupId(String batchId, String groupId) {
		List<ViewUnitTemplate> list = dao.query(ViewUnitTemplate.class,
				Cnd.where("BATCH_ID", "=", batchId).and("GROUP_ID", "=", groupId).asc("TEMPLATEORDERID"));
		return list;
	}

	//获得该单位下所有需要填报的报表
	public List<ViewUnitTemplate> getAllFillTemplateDataId(String batchId) {
		List<ViewUnitTemplate> list = dao.query(ViewUnitTemplate.class,
				Cnd.where("BATCH_ID", "=", batchId).asc("TEMPLATEORDERID"));
		return list;
	}

	//数据清除功能（需要修改）
	public List<RptBatchTemplateRel> getAllTemplateDataByUnitId(String annualId, String unitId) {
		//先拿到所有的该单位下 这个版本的所有报表实例ID
		List<RptBatchTemplateRel> rptUnitTemplateRelList = dao.query(RptBatchTemplateRel.class,
				Cnd.where("UNITID", "=", unitId).and("ANNUALID", "=", annualId));
		return rptUnitTemplateRelList;
	}

	//获得所有报表的表间校核信息（需要修改）
	public Map getAllTemplateBetweenValidation(String annualId, String dzzId, String whereSql, int pageNum,
			int pageSize) {
		//拼表名
		String tableName = "rpt_vali_info_" + annualId;
		String strSql = "";
		String strCountSql = "";
		if (whereSql.equals("")) {
			strSql = "select t1.ID as id,t1.CELL_EXP as CELL_EXP,t1.VALIDATION_TYPE as VALIDATION_TYPE,t1.ACTUAL_EXP as ACTUAL_EXP,t1.source_exp as SOURCE_EXP,t1.template_id as TEMPLATEID,t1.cell_id as CELL_ID,t2.name as TEMPLATENAME "
					+ "from " + tableName + " t1 " + "left join rpt_template t2 on t1.template_id = t2.templateid"
					+ " where t1.dzz_id = @dzzId and t1.validation_state = '2'";
			strCountSql = "select count(*) from " + tableName + " t1 "
					+ "left join rpt_template t2 on t1.template_id = t2.templateid"
					+ " where t1.dzz_id = @dzzId and t1.validation_state = '2'";
		} else {
			strSql = "select t1.id as id,t1.CELL_EXP as CELL_EXP,t1.VALIDATION_TYPE as VALIDATION_TYPE,t1.ACTUAL_EXP as ACTUAL_EXP,t1.source_exp as source_exp,t1.template_id as TEMPLATEID,t1.cell_id as CELL_ID,t2.name as TEMPLATENAME "
					+ "from " + tableName + " t1 " + "left join rpt_template t2 on t1.template_id = t2.templateid"
					+ " where t1.dzz_id = @dzzId and t1.validation_state = '2' and " + whereSql;
			strCountSql = "select count(*) from " + tableName + " t1 "
					+ "left join rpt_template t2 on t1.template_id = t2.templateid"
					+ " where t1.dzz_id = @dzzId and t1.validation_state = '2' and " + whereSql;
		}

		Sql sql = Sqls.create(strSql);
		sql.params().set("dzzId", dzzId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<ValidationInfo> validationInfoList = new ArrayList<ValidationInfo>();
				while (rs.next()) {
					ValidationInfo validationInfo = new ValidationInfo();
					validationInfo.setId(rs.getString("ID"));
					validationInfo.setSourceExp(rs.getString("SOURCE_EXP"));
					validationInfo.setCellId(rs.getString("CELL_ID"));
					validationInfo.setTemplateId(rs.getString("TEMPLATEID"));
					validationInfo.setTemplateName(rs.getString("TEMPLATENAME"));
					validationInfo.setCellExp(rs.getString("CELL_EXP"));
					validationInfo.setActualExp(rs.getString("ACTUAL_EXP"));
					validationInfo.setValidationType(rs.getString("VALIDATION_TYPE"));
					validationInfoList.add(validationInfo);
				}
				return validationInfoList;
			}
		});
		sql.setPager(dao.createPager(pageNum, pageSize));
		dao.execute(sql);

		Sql countSql = Sqls.create(strCountSql);
		countSql.params().set("dzzId", dzzId);
		countSql.setCallback(Sqls.callback.integer());
		dao.execute(countSql);

		Map<String, Object> result = new HashMap<String, Object>();
		result.put("Rows", sql.getList(ValidationInfo.class));
		result.put("Total", countSql.getObject(Integer.class));
		return result;
	}

	//创建一张视图
	public void createTemplateValidationView(String annualId) {
		//拼表名
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		Sql strSql = Sqls.create("create or replace view view_vali_" + annualId + " "
				+ " as select t1.ID,t1.TEMPLATE_DATA_ID,t1.SOURCE_EXP,t1.CELL_EXP,t1.ACTUAL_EXP,t1.VALIDATION_TYPE,t1.VALIDATION_STATE,t1.CELL_ID,t3.NAME AS TEMPLATENAME,t3.TEMPLATEID from "
				+ tableName + " t1" + " left join RPT_UNIT_TEMPLATE_R t2 on t1.template_data_id = t2.id "
				+ "left join RPT_TEMPLATE t3 on t2.templateid = t3.templateid");
		dao.execute(strSql);
	}

	//根据ID查询视图，得到一个验证实体
	public ValidationInfo getAValidationInfoById(String id, String annualId) {
		//拼表名
		String tableName = "rpt_vali_info_" + annualId;
		String strSql = "select ID,SOURCE_EXP,CELL_EXP,ACTUAL_EXP,VALIDATION_TYPE,VALIDATION_STATE,CELL_ID,TEMPLATE_ID from "
				+ tableName + " where ID = @ID";
		Sql sql = Sqls.create(strSql);
		sql.params().set("ID", id);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				ValidationInfo validationInfo = new ValidationInfo();
				while (rs.next()) {
					validationInfo.setId(rs.getString("ID"));
					validationInfo.setSourceExp(rs.getString("SOURCE_EXP"));
					validationInfo.setCellId(rs.getString("CELL_ID"));
					validationInfo.setTemplateId(rs.getString("TEMPLATE_ID"));
					validationInfo.setCellExp(rs.getString("CELL_EXP"));
					validationInfo.setValidationType(rs.getString("VALIDATION_TYPE"));
					validationInfo.setActualExp(rs.getString("ACTUAL_EXP"));//add by qiyuxiong 2017.8.23
				}
				return validationInfo;
			}
		});
		dao.execute(sql);
		return sql.getObject(ValidationInfo.class);
	}

	public List<ValidationInfo> getValidationInfoByTemplateIdType(String annualId, String templateId, String dzzId) {
		//拼表名
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		String strSql = "select ID,TEMPLATE_ID,SOURCE_EXP,CELL_EXP,ACTUAL_EXP,VALIDATION_TYPE,VALIDATION_STATE,CELL_ID,DZZ_ID from "
				+ tableName
				+ " where TEMPLATE_ID = @templateId and DZZ_ID = @dzzId and VALIDATION_TYPE in ('1','2','3') and VALIDATION_STATE = '2'";
		Sql sql = Sqls.create(strSql);
		sql.params().set("templateId", templateId).set("dzzId", dzzId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<ValidationInfo> validationInfoList = new ArrayList<ValidationInfo>();
				while (rs.next()) {
					ValidationInfo validationInfo = new ValidationInfo();
					validationInfo.setId(rs.getString("ID"));
					validationInfo.setSourceExp(rs.getString("SOURCE_EXP"));
					validationInfo.setCellId(rs.getString("CELL_ID"));
					validationInfo.setActualExp(rs.getString("ACTUAL_EXP"));
					validationInfo.setCellExp(rs.getString("CELL_EXP"));
					validationInfo.setValidationType(rs.getString("VALIDATION_TYPE"));
					validationInfoList.add(validationInfo);
				}
				return validationInfoList;
			}
		});
		dao.execute(sql);
		return sql.getList(ValidationInfo.class);
	}

	//修改报表的状态
	public void updateTemplateDataExamine(String batchId, String templateId, String examineState) {
		String strSql = "update RPT_BATCH_TEMPLATE_R set EXAMINE_STATE = @examineState where BATCH_ID = @batchId and TEMPLATE_ID = @templateId";
		Sql sql = Sqls.create(strSql);
		sql.params().set("examineState", examineState).set("batchId", batchId).set("templateId", templateId);
		dao.execute(sql);
	}

	//根据批次得到当前单位需要填报的所有报表
	public List<RptBatchTemplateRel> getAllTemplateIdByBatchId(String batchId) {
		return dao.query(RptBatchTemplateRel.class, Cnd.where("BATCH_ID", "=", batchId));
	}

	//根据批次，批量修改报表的状态
	public void updateTemplateDataExamineByBatch(String batchId, String examineState) {
		String strSql = "update RPT_BATCH_TEMPLATE_R set EXAMINE_STATE = @examineState where BATCH_ID = @batchId";
		Sql sql = Sqls.create(strSql);
		sql.params().set("examineState", examineState).set("batchId", batchId);
		dao.execute(sql);
	}

	//判断单张表中的所有校核信息是否都通过了，都通过了将该张报表的校核状态改成1（校核通过），如果不通过则为2（未通过）
	public void batchValidationTemplateDataValState(String dzzId, String annualId, String batchId) {
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		String strTemps = " SELECT TEMPLATE_ID FROM $tableName WHERE VALIDATION_STATE = '2' " + " AND DZZ_ID = @dzzId "
				+ " GROUP BY TEMPLATE_ID ";
		Sql tempSql = Sqls.create(strTemps);
		tempSql.params().set("dzzId", dzzId);
		tempSql.vars().set("tableName", tableName);
		tempSql.setCallback(Sqls.callback.strList());
		dao.execute(tempSql);
		List<String> temps = tempSql.getList(String.class);

		String strSql = "update RPT_BATCH_TEMPLATE_R a set a.VALIDATION_STATE = (case when a.TEMPLATE_ID in ( "
				+ " $templateIds ) then '2' else '1' end) where a.BATCH_ID = @batchId ";
		Sql sql = Sqls.create(strSql);
		sql.params().set("batchId", batchId);
		sql.vars().set("templateIds", CommonUtil.convertIDList(temps));
		dao.execute(sql);
	}

	//判断单张表中的所有校核信息是否都通过了，都通过了将该张报表的校核状态改成1（校核通过），如果不通过则为2（未通过）
	public void validationTemplateDataValState(String dzzId, String templateId, String annualId, String batchId) {
		//拼表名
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		String strSql = "select count(*) from " + tableName
				+ " where VALIDATION_STATE = '2' and TEMPLATE_ID = @templateId and DZZ_ID = @dzzId";
		Sql sql = Sqls.create(strSql);
		sql.setCallback(Sqls.callback.integer());
		sql.params().set("templateId", templateId).set("dzzId", dzzId);
		dao.execute(sql);
		Integer valCount = sql.getObject(Integer.class);
		if (valCount == 0) {
			/*String valStrSql = "update RPT_BATCH_TEMPLATE_R set VALIDATION_STATE = '1' where BATCH_ID = @batchId and TEMPLATE_ID = @templateId";
			Sql valSql = Sqls.create(valStrSql);
			valSql.params().set("batchId", batchId).set("templateId", templateId);
			dao.execute(valSql);*/
			updateReportValidationState(batchId, templateId, "1");
		} else {
			updateReportValidationState(batchId, templateId, "2");
		}

	}

	private Map<String, Cell> generateCellMap(List<Cell> list) {
		if (list == null)
			return null;
		Map<String, Cell> map = new HashMap<String, Cell>();
		for (Cell cell : list) {
			map.put(cell.getAbsRowNum() + "_" + cell.getAbsColNum(), cell);
		}
		return map;
	}

	//拆开公式，判断每一个单元格是否是为空的，如果为空的话，就把这个单元格从公式中去掉
	public List<CellFormulaEntity> cellFormulaHandle(List<CellFormulaEntity> cellFormulaEntityList, String templateId) {
		List<CellFormulaEntity> cellFormulaEntityListNew = new ArrayList<CellFormulaEntity>();

		List<Cell> cellList = cellService.getCellStrategyByTemplateId(templateId);
		Map<String, Cell> cellMap = generateCellMap(cellList);
		for (CellFormulaEntity cellFormulaEntity : cellFormulaEntityList) {
			String[] array = cellFormulaEntity.getCellFormula().split("[\\+\\-\\*/]");
			String cellFormula = cellFormulaEntity.getCellFormula();
			for (String str : array) {
				String num = str.replaceAll("[^(A-Za-z)]", "");
				String colNum = CommonUtil.letterToNum(num);
				String rowNum = str.replaceAll("[^(0-9)]", "");
				/*Cell cell = dao.fetch(Cell.class, Cnd.where("ABS_ROW_NUM", "=", rowNum).and("ABS_COL_NUM", "=", colNum)
						.and("TEMPLATE_ID", "=", templateId));*/
				Cell cell = cellMap.get(rowNum + "_" + colNum);
				if (null != cell) {
					if (cell.getCellType().equals(CellType.cellNull.getKey())) {
						if (cellFormulaEntity.getCellFormula().indexOf("+" + str) > 0) {
							cellFormula = cellFormula.replace("+" + str, "");
						} else {
							cellFormula = cellFormula.replace(str, "");
						}
					}
				}
			}
			//截取字符串之后，如果公式最开始得时候是个符号，默认给去掉
			if (cellFormula.length() > 0) {
				if (cellFormula.substring(0, 1).equals("+") || cellFormula.substring(0, 1).equals("-")
						|| cellFormula.substring(0, 1).equals("*")) {
					cellFormula = cellFormula.substring(1, cellFormula.length());
				}
			}
			cellFormulaEntity.setCellFormula(cellFormula);
			cellFormulaEntityListNew.add(cellFormulaEntity);
		}
		return cellFormulaEntityListNew;
	}

	//修改报表校核状态
	public void updateReportValidationState(String batchId, String templateId, String validationState) {
		String valStrSql = "update RPT_BATCH_TEMPLATE_R set VALIDATION_STATE = @validationState where BATCH_ID = @batchId and TEMPLATE_ID = @templateId";
		Sql valSql = Sqls.create(valStrSql);
		valSql.params().set("batchId", batchId).set("templateId", templateId).set("validationState", validationState);
		dao.execute(valSql);
	}

	//获取celldata(打印获取报表数据:汇总)
	public List<CellDataValue> getAllCellDataValueToPorint(String templateId, String annualId, String dzzId,
			String summary) {
		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		String sqlString = "select CELL_DATA_ID,CELL_VALUE,BATCH_ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID,TEMPLATE_ID from "
				+ tableName + " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId and SUM_STATUS =@summary";
		Sql sql = Sqls.create(sqlString);
		sql.params().set("dzzId", dzzId).set("templateId", templateId).set("summary", summary);
		sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<CellDataValue> list = new LinkedList<CellDataValue>();
				while (rs.next()) {
					CellDataValue cellDataValue = new CellDataValue();
					cellDataValue.setCellDataId(rs.getString("CELL_DATA_ID"));
					cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
					cellDataValue.setBatchId(rs.getString("BATCH_ID"));
					cellDataValue.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					cellDataValue.setAbsColNum(rs.getString("ABS_COL_NUM"));
					cellDataValue.setRelRowNum(rs.getString("REL_ROW_NUM"));
					cellDataValue.setRelColNum(rs.getString("REL_COL_NUM"));
					cellDataValue.setDzzId(rs.getString("DZZ_ID"));
					cellDataValue.setTemplateId(rs.getString("TEMPLATE_ID"));
					list.add(cellDataValue);
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(CellDataValue.class);
	}

	//获取celldata(打印获取报表数据:非汇总)
	public List<CellDataValue> getAllCellDataValueToPorint(String templateId, String annualId, String dzzId) {
		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		String sqlString = "select CELL_DATA_ID,CELL_VALUE,BATCH_ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID,TEMPLATE_ID from "
				+ tableName + " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId";
		Sql sql = Sqls.create(sqlString);
		sql.params().set("dzzId", dzzId).set("templateId", templateId);
		sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<CellDataValue> list = new LinkedList<CellDataValue>();
				while (rs.next()) {
					CellDataValue cellDataValue = new CellDataValue();
					cellDataValue.setCellDataId(rs.getString("CELL_DATA_ID"));
					cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
					cellDataValue.setBatchId(rs.getString("BATCH_ID"));
					cellDataValue.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					cellDataValue.setAbsColNum(rs.getString("ABS_COL_NUM"));
					cellDataValue.setRelRowNum(rs.getString("REL_ROW_NUM"));
					cellDataValue.setRelColNum(rs.getString("REL_COL_NUM"));
					cellDataValue.setDzzId(rs.getString("DZZ_ID"));
					cellDataValue.setTemplateId(rs.getString("TEMPLATE_ID"));
					list.add(cellDataValue);
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(CellDataValue.class);
	}

	//获取所有celldata(打印获取报表数据:汇总)
	public List<CellDataValue> getAllFillValue(String annualId, String templateId, String dzzId, String summary) {
		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		//	String sqlString = "select CELL_DATA_ID,CELL_VALUE,BATCH_ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID,TEMPLATE_ID from "
		//			+ tableName + " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId and SUM_STATUS =@summary";
		String sqlString = "select t.cell_id,t.CELL_DATA_ID,t.CELL_VALUE,t.BATCH_ID,t.ABS_ROW_NUM,t.ABS_COL_NUM,t.REL_ROW_NUM,t.REL_COL_NUM,t.DZZ_ID,t.TEMPLATE_ID,t.SUM_STATUS,t1.cell_type from "
				+ tableName
				+ "  t  right  JOIN RPT_CELL   t1 on (t.cell_id = t1.cell_id)  where t.DZZ_ID = @dzzId and t.TEMPLATE_ID = @templateId and t.SUM_STATUS =@summary";
		Sql sql = Sqls.create(sqlString);
		sql.params().set("dzzId", dzzId).set("templateId", templateId).set("summary", summary);
		sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<CellDataValue> list = new LinkedList<CellDataValue>();
				while (rs.next()) {
					CellDataValue cellDataValue = new CellDataValue();
					cellDataValue.setCellDataId(rs.getString("CELL_DATA_ID"));
					cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
					cellDataValue.setBatchId(rs.getString("BATCH_ID"));
					cellDataValue.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					cellDataValue.setAbsColNum(rs.getString("ABS_COL_NUM"));
					cellDataValue.setRelRowNum(rs.getString("REL_ROW_NUM"));
					cellDataValue.setRelColNum(rs.getString("REL_COL_NUM"));
					cellDataValue.setDzzId(rs.getString("DZZ_ID"));
					cellDataValue.setTemplateId(rs.getString("TEMPLATE_ID"));
					cellDataValue.setCellType(rs.getString("CELL_TYPE"));
					list.add(cellDataValue);
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(CellDataValue.class);
	}

	//获取所有celldata(打印获取报表数据:非汇总)
	public List<CellDataValue> getAllFillValue(String annualId, String templateId, String dzzId) {
		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		//	String sqlString = "select CELL_DATA_ID,CELL_VALUE,BATCH_ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID,TEMPLATE_ID from "
		//			+ tableName + " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId";
		String sqlString = "select t.cell_id,t.CELL_DATA_ID,t.CELL_VALUE,t.BATCH_ID,t.ABS_ROW_NUM,t.ABS_COL_NUM,t.REL_ROW_NUM,t.REL_COL_NUM,t.DZZ_ID,t.TEMPLATE_ID,t1.cell_type from "
				+ tableName
				+ " t  right  JOIN RPT_CELL  t1 on (t.cell_id = t1.cell_id)  where t.DZZ_ID = @dzzId and t.TEMPLATE_ID = @templateId";
		Sql sql = Sqls.create(sqlString);
		sql.params().set("dzzId", dzzId).set("templateId", templateId);
		sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<CellDataValue> list = new LinkedList<CellDataValue>();
				while (rs.next()) {
					CellDataValue cellDataValue = new CellDataValue();
					cellDataValue.setCellDataId(rs.getString("CELL_DATA_ID"));
					cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
					cellDataValue.setBatchId(rs.getString("BATCH_ID"));
					cellDataValue.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					cellDataValue.setAbsColNum(rs.getString("ABS_COL_NUM"));
					cellDataValue.setRelRowNum(rs.getString("REL_ROW_NUM"));
					cellDataValue.setRelColNum(rs.getString("REL_COL_NUM"));
					cellDataValue.setDzzId(rs.getString("DZZ_ID"));
					cellDataValue.setTemplateId(rs.getString("TEMPLATE_ID"));
					cellDataValue.setCellType(rs.getString("CELL_TYPE"));
					list.add(cellDataValue);
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(CellDataValue.class);
	}

	//获取所有celldata(非汇总) 有单元格属性
	public List<CellDataValue> getAllCellDataType(String annualId, String templateId, String batchId, String dzzId) {

		//先通过报表的ID获得报表中的所有数据区域
		List<Cell> cellList = dao.query(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId));

		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		Sql strSql = Sqls
				.create("select CELL_DATA_ID,CELL_VALUE,BATCH_ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID,TEMPLATE_ID from "
						+ tableName + " where ABS_ROW_NUM = @absRowNum and ABS_COL_NUM = @absColNum "
						+ " and DZZ_ID = @dzzId and TEMPLATE_ID = @templateId");
		//存放指标的LIST
		List<CellDataValue> cellDataValueList = new ArrayList<CellDataValue>();
		//首先先给所有的数据区域赋值为空
		for (Cell cell : cellList) {

			//通过报表实例ID获得当前这个报表已经生成的数据，然后根据数据区域中的行列坐标与指标值表中的行列坐标进行对比，如果有值，把值赋进去，如果没值就拉倒
			strSql.params().set("absRowNum", cell.getAbsRowNum()).set("absColNum", cell.getAbsColNum())
					.set("dzzId", dzzId).set("templateId", templateId);
			/*strSql.params().set("templateDataId", templateDataId).set("absRowNum", 19).set("absColNum", 9);*/
			//设置回调，执行SQL语句
			strSql.setCallback(new SqlCallback() {
				@Override
				public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
					CellDataValue cellDataValue = new CellDataValue();
					while (rs.next()) {
						cellDataValue.setCellDataId(rs.getString("CELL_DATA_ID"));
						cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
						cellDataValue.setBatchId(rs.getString("BATCH_ID"));
						cellDataValue.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
						cellDataValue.setAbsColNum(rs.getString("ABS_COL_NUM"));
						cellDataValue.setRelRowNum(rs.getString("REL_ROW_NUM"));
						cellDataValue.setRelColNum(rs.getString("REL_COL_NUM"));
						cellDataValue.setDzzId(rs.getString("DZZ_ID"));
						cellDataValue.setTemplateId(rs.getString("TEMPLATE_ID"));
					}
					return cellDataValue;
				}
			});

			dao.execute(strSql);

			CellDataValue cellDataValueEntity = strSql.getObject(CellDataValue.class);
			//判断SQL执行的结果，如果不等于Null,说明该指标已经有生成好的数据，那么就直接将它放入LIST中，如果为null，就要先给这个指标进行初始化设置
			if (cellDataValueEntity.getCellDataId() != null) {
				if (cell.getCellType() == CellType.cellNull.getKey()) {
					cellDataValueEntity.setCellValue("--");
				}
				cellDataValueList.add(cellDataValueEntity);
			} else {
				CellDataValue cellDataValue = new CellDataValue();
				cellDataValue.setAbsColNum(cell.getAbsColNum() + "");
				cellDataValue.setAbsRowNum(cell.getAbsRowNum() + "");
				cellDataValue.setRelColNum(cell.getRelColNum() + "");
				cellDataValue.setRelRowNum(cell.getRelRowNum() + "");
				if (cell.getCellType().equals(CellType.cellNull.getKey())) {
					cellDataValue.setCellValue("--");
				} else {
					cellDataValue.setCellValue("0");
				}
				cellDataValue.setBatchId(batchId);
				cellDataValue.setDzzId(dzzId);
				cellDataValue.setTemplateId(templateId);
				cellDataValueList.add(cellDataValue);
			}
		}
		return cellDataValueList;
	}

	//获取所有celldata(非汇总) 有单元格属性
	public List<CellDataValue> getAllCellDataType(String annualId, String templateId, String batchId, String dzzId,
			String summary) {

		//先通过报表的ID获得报表中的所有数据区域
		List<Cell> cellList = dao.query(Cell.class, Cnd.where("TEMPLATE_ID", "=", templateId));

		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		Sql strSql = Sqls
				.create("select CELL_DATA_ID,CELL_VALUE,BATCH_ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID,TEMPLATE_ID from "
						+ tableName + " where ABS_ROW_NUM = @absRowNum and ABS_COL_NUM = @absColNum "
						+ " and DZZ_ID = @dzzId and TEMPLATE_ID = @templateId and SUM_STATUS = @summary");
		//存放指标的LIST
		List<CellDataValue> cellDataValueList = new ArrayList<CellDataValue>();
		//首先先给所有的数据区域赋值为空
		for (Cell cell : cellList) {

			//通过报表实例ID获得当前这个报表已经生成的数据，然后根据数据区域中的行列坐标与指标值表中的行列坐标进行对比，如果有值，把值赋进去，如果没值就拉倒
			strSql.params().set("absRowNum", cell.getAbsRowNum()).set("absColNum", cell.getAbsColNum())
					.set("dzzId", dzzId).set("templateId", templateId).set("summary", 1);
			/*strSql.params().set("templateDataId", templateDataId).set("absRowNum", 19).set("absColNum", 9);*/
			//设置回调，执行SQL语句
			strSql.setCallback(new SqlCallback() {
				@Override
				public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
					CellDataValue cellDataValue = new CellDataValue();
					while (rs.next()) {
						cellDataValue.setCellDataId(rs.getString("CELL_DATA_ID"));
						cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
						cellDataValue.setBatchId(rs.getString("BATCH_ID"));
						cellDataValue.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
						cellDataValue.setAbsColNum(rs.getString("ABS_COL_NUM"));
						cellDataValue.setRelRowNum(rs.getString("REL_ROW_NUM"));
						cellDataValue.setRelColNum(rs.getString("REL_COL_NUM"));
						cellDataValue.setDzzId(rs.getString("DZZ_ID"));
						cellDataValue.setTemplateId(rs.getString("TEMPLATE_ID"));
					}
					return cellDataValue;
				}
			});

			dao.execute(strSql);

			CellDataValue cellDataValueEntity = strSql.getObject(CellDataValue.class);
			//判断SQL执行的结果，如果不等于Null,说明该指标已经有生成好的数据，那么就直接将它放入LIST中，如果为null，就要先给这个指标进行初始化设置
			if (cellDataValueEntity.getCellDataId() != null) {
				if (cell.getCellType() == CellType.cellNull.getKey()) {
					cellDataValueEntity.setCellValue("--");
				}
				cellDataValueList.add(cellDataValueEntity);
			} else {
				CellDataValue cellDataValue = new CellDataValue();
				cellDataValue.setAbsColNum(cell.getAbsColNum() + "");
				cellDataValue.setAbsRowNum(cell.getAbsRowNum() + "");
				cellDataValue.setRelColNum(cell.getRelColNum() + "");
				cellDataValue.setRelRowNum(cell.getRelRowNum() + "");
				if (cell.getCellType().equals(CellType.cellNull.getKey())) {
					cellDataValue.setCellValue("--");
				} else {
					cellDataValue.setCellValue("0");
				}
				cellDataValue.setBatchId(batchId);
				cellDataValue.setDzzId(dzzId);
				cellDataValue.setTemplateId(templateId);
				cellDataValueList.add(cellDataValue);
			}
		}
		return cellDataValueList;
	}

	//根据批次ID，修改所有的报表的状态
	public void updateTemplateStateByBatchId(String batchId, String examineState) {
		Sql strSql = Sqls
				.create("update rpt_batch_template_r set examine_state = @examineState where batch_id=@batchId");
		strSql.params().set("examineState", examineState).set("batchId", batchId);
		dao.execute(strSql);
	}

	public String summaryCountDzz(String annualId, String templateId, final String batchId, String dzzId) {
		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		Sql strSql = Sqls.create("select count(*) from " + tableName
				+ " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId and SUM_STATUS = @summary");
		strSql.params().set("dzzId", dzzId).set("templateId", templateId).set("summary", "1");
		strSql.setCallback(Sqls.callback.str());
		dao.execute(strSql);
		return strSql.getObject(String.class);
	}

	public int cellDataOnloadByDzzId(String sqlStr, String dzzIdS, String startTime, String endTime,
			String sessionDzzId) {
		if (StringUtils.isBlank(sqlStr)) {
			return 0;
		} else {
			Sql sql = Sqls.create(sqlStr);
			sql.params().set("startTimeConditionVal", startTime).set("endTimeConditionVal", endTime);
			sql.vars().set("unitConditionVal", "(" + dzzIdS + ")");
			if (sqlStr.contains("sessionDzzId") == true) {
				sql.vars().set("sessionDzzId", "'" + sessionDzzId + "'");
			}
			sql.setCallback(Sqls.callback.integer());
			dao.execute(sql);
			try {
				return sql.getObject(Integer.class);
			} catch (Exception npe) {
				return 0;
			}
		}
	}

	//报表所有的汇总的总方法
	@Transactional
	public void reportSummary(ReportRequest reportRequest, String dzzId, String annualId, List<String> dzzIdList,
			List<String> explainIdTMList, List<String> explainIdSZList, List<String> explainIdWBList, String batchId) {
		reportSummaryService.sumReportData(reportRequest);
		//rptCellExplainSummaryService.deleteByDzzIdAnIsSummary(dzzId, annualId);
		//dzzIdList.add(dzzId);
		rptCellExplainSummaryService.rptCellExplainSummaryTiaoMu(dzzIdList, explainIdTMList, annualId, dzzId);
		rptCellExplainSummaryService.rptCellExplainSummaryShuZi(dzzIdList, explainIdSZList, annualId, dzzId);
		rptCellExplainSummaryService.rptCellExplainSummaryWenBen(dzzIdList, explainIdWBList, annualId, dzzId);
		rptDealWithService.updateBatchState(batchId, "50", null);
		updateTemplateStateByBatchId(batchId, "50");
	}

	private List<String> getTemplateIdListByBatchId(String batchId) {
		List<String> templateIdList = new ArrayList<String>();
		List<RptBatchTemplateRel> rptBatchTemplateRelList = rptDealWithService.queryBatchTemplateRel(batchId);
		for (RptBatchTemplateRel rptBatchTemplateRel : rptBatchTemplateRelList) {
			templateIdList.add(rptBatchTemplateRel.getTemplateId());
		}
		return templateIdList;
	}

	//只汇总报表基本数据
	//@Transactional
	public void controllerCallReportSummary(HttpSession session, List<String> templateIdsList, List<String> dzzIdList,
			final String annualId, final String dzzId, final String batchId) {
		if (templateIdsList == null || templateIdsList.isEmpty()) {
			templateIdsList = getTemplateIdListByBatchId(batchId);
			cellDataService.deleteAllCellDataByDzzId(annualId, dzzId);
			/*Future<Void> res = ExecutorPool.getInstance().getCachePool().submit(new Callable<Void>() {
			
				@Override
				public Void call() throws Exception {
					cellDataService.deleteAllCellDataByDzzId(annualId, dzzId);
					return null;
				}
			});
			try {
				res.get();
			} catch (InterruptedException e) {
				e.printStackTrace();
			} catch (ExecutionException e) {
				e.printStackTrace();
			}*/
		} else {
			cellDataService.deleteAllCellDataByDzzIdAndTemplateId(annualId, dzzId, templateIdsList);
		}

		List<Cell> cellList = new ArrayList<Cell>();
		for (String templateId : templateIdsList) {
			List<Cell> tempList = cellService.getCellStrategyByTemplateId(templateId);
			for (Cell cell : tempList) {
				//判断下不为预设的才进LIST
				if (cell.getCellType().equals("1") || cell.getCellType().equals("2")
						|| cell.getCellType().equals("3")) {
					//cellIdList.add(cell.getCellId());
					cellList.add(cell);
				}
			}
		}

		if (session != null) {
			session.setAttribute("generationSize", cellList.size());
			session.setAttribute("generationCounter", new AtomicInteger(0));
		}
		List<CellData> cellDataList = reportSummaryNewService.reportValueSummary(session, cellList, dzzIdList, annualId,
				dzzId);
		//生成预设类指标，然后也入库
		RptBatch rptBatch = rptDealWithService.queryBatch(batchId);
		String kaishishijian = "开始时间：" + TimeUtil.dateFormatString(rptBatch.getStartTime());
		String jiesushijian = "截止时间：" + TimeUtil.dateFormatString(rptBatch.getEndTime());
		String qizhishijian = "起止时间：" + TimeUtil.dateFormatString(rptBatch.getStartTime()) + " 至   "
				+ TimeUtil.dateFormatString(rptBatch.getEndTime());
		RptUnit rptUnit = rptUnitService.getARptUnitByDzzIdAnnualId(dzzId, annualId);
		String tianbaodanwei = "填报单位：" + rptUnit.getFullName();
		String lianxiren = rptUnit.getRelMan();
		for (String templateId : templateIdsList) {
			List<Cell> cellPresetList = cellService.getRawCellsByTemplateIdAndType(templateId, CellType.quote);
			for (Cell cell : cellPresetList) {
				String cellValue = "";
				switch (cell.getQuoteType()) {
				case "1":
					cellValue = tianbaodanwei;
					break;
				case "2":
					cellValue = jiesushijian;
					break;
				case "3":
					cellValue = kaishishijian;
					break;
				case "4":
					cellValue = qizhishijian;
					break;
				case "5":
					cellValue = lianxiren;
					break;
				default:
					break;
				}
				CellData cellData = new CellData(cell, cellValue, dzzId);
				cellData.setInsertDate(new Date());
				cellData.setSumStatus("1");
				cellDataList.add(cellData);
			}
		}

		reportSummaryNewService.saveReportValueSummary(cellDataList, annualId);
		if (session != null) {
			session.setAttribute("generationSize", 0);
		}
	}

	//只汇总报表说明数据
	//@Transactional
	public void controllerCallFxplainSummary(HttpSession session, List<String> templateIdList,
			List<String> treadExplainDzzIdList, String annualId, String dzzId, String batchId) {
		if (templateIdList == null || templateIdList.isEmpty()) {
			templateIdList = getTemplateIdListByBatchId(batchId);
			rptExplainCellFillService.deleteAllCellExplainByDzzId(annualId, dzzId);
		} else {
			rptExplainCellFillService.deleteAllCellExplainByTemplate(annualId, dzzId, templateIdList);
		}
		if (session != null) {
			session.setAttribute("generationSize", templateIdList.size());
			session.setAttribute("generationCounter", new AtomicInteger(0));
		}
		Map<String, List> map = rptCellExplainSummaryNewService.reporExplainValueSummary(session, templateIdList,
				treadExplainDzzIdList, annualId, dzzId);
		rptExplainCellFillService.insertExplainCellValueList(annualId, map.get("reportExplainFillEntityList"));
		rptExplainCellFillService.insertExplainCellTextValueList(annualId, map.get("reportExplainTextFillEntityList"));
		if (session != null) {
			session.setAttribute("generationSize", 0);
		}
	}

	private static final Log logger = LogFactory.getLog(ReportFillService.class);

	//报表基本数据和报表说明一起汇总
	//@Transactional
	public void controllerCallAllSummary(HttpSession session, List<String> templateIdList, List<String> dzzIdList,
			String annualId, String dzzId, String batchId) {
		controllerCallReportSummary(session, templateIdList, dzzIdList, annualId, dzzId, batchId);
		controllerCallFxplainSummary(session, templateIdList, dzzIdList, annualId, dzzId, batchId);
	}

	//获取所有celldata(打印获取报表数据:汇总) 排除空表
	public List<CellDataValue> getAllFillValueNoEmptyRpt(String annualId, String templateId, String dzzId,
			String summary) {
		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		//	String sqlString = "select CELL_DATA_ID,CELL_VALUE,BATCH_ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID,TEMPLATE_ID from "
		//			+ tableName + " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId and SUM_STATUS =@summary";
		String sqlString = "select t.cell_id,t.CELL_DATA_ID,t.CELL_VALUE,t.BATCH_ID,t.ABS_ROW_NUM,t.ABS_COL_NUM,t.REL_ROW_NUM,t.REL_COL_NUM,t.DZZ_ID,t.TEMPLATE_ID,t.SUM_STATUS,t.cell_type from "
				+ tableName
				+ "  t   where t.DZZ_ID = @dzzId and t.TEMPLATE_ID = @templateId and t.SUM_STATUS =@summary "
				+ " and EXISTS (SELECT count(1) from " + tableName + " d "
				+ " where d.DZZ_ID=@dzzId and d.TEMPLATE_ID= @templateId and d.CELL_TYPE in ('1','2','3') "
				+ " and d.CELL_VALUE<>'0' HAVING count(1)>0)";
		Sql sql = Sqls.create(sqlString);
		sql.params().set("dzzId", dzzId).set("templateId", templateId).set("summary", summary);
		sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<CellDataValue> list = new LinkedList<CellDataValue>();
				while (rs.next()) {
					CellDataValue cellDataValue = new CellDataValue();
					cellDataValue.setCellDataId(rs.getString("CELL_DATA_ID"));
					cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
					cellDataValue.setBatchId(rs.getString("BATCH_ID"));
					cellDataValue.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					cellDataValue.setAbsColNum(rs.getString("ABS_COL_NUM"));
					cellDataValue.setRelRowNum(rs.getString("REL_ROW_NUM"));
					cellDataValue.setRelColNum(rs.getString("REL_COL_NUM"));
					cellDataValue.setDzzId(rs.getString("DZZ_ID"));
					cellDataValue.setTemplateId(rs.getString("TEMPLATE_ID"));
					cellDataValue.setCellType(rs.getString("CELL_TYPE"));
					//cellDataValue.setAreaType(rs.getString("AREA_TYPE"));
					list.add(cellDataValue);
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(CellDataValue.class);
	}

	//获取所有celldata(打印获取报表数据:非汇总) 排除空表
	public List<CellDataValue> getAllFillValueNoEmptyRpt(String annualId, String templateId, String dzzId) {
		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		//	String sqlString = "select CELL_DATA_ID,CELL_VALUE,BATCH_ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID,TEMPLATE_ID from "
		//			+ tableName + " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId";
		String sqlString = "select t.cell_id,t.CELL_DATA_ID,t.CELL_VALUE,t.BATCH_ID,t.ABS_ROW_NUM,t.ABS_COL_NUM,t.REL_ROW_NUM,t.REL_COL_NUM,t.DZZ_ID,t.TEMPLATE_ID,t.cell_type from "
				+ tableName + " t   where t.DZZ_ID = @dzzId and t.TEMPLATE_ID = @templateId "
				+ " and EXISTS (SELECT count(1) from " + tableName + " d "
				+ " where d.DZZ_ID=@dzzId and d.TEMPLATE_ID= @templateId and d.CELL_TYPE in ('1','2','3') "
				+ " and d.CELL_VALUE<>'0' HAVING count(1)>0)";
		Sql sql = Sqls.create(sqlString);
		sql.params().set("dzzId", dzzId).set("templateId", templateId);
		sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<CellDataValue> list = new LinkedList<CellDataValue>();
				while (rs.next()) {
					CellDataValue cellDataValue = new CellDataValue();
					cellDataValue.setCellDataId(rs.getString("CELL_DATA_ID"));
					cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
					cellDataValue.setBatchId(rs.getString("BATCH_ID"));
					cellDataValue.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					cellDataValue.setAbsColNum(rs.getString("ABS_COL_NUM"));
					cellDataValue.setRelRowNum(rs.getString("REL_ROW_NUM"));
					cellDataValue.setRelColNum(rs.getString("REL_COL_NUM"));
					cellDataValue.setDzzId(rs.getString("DZZ_ID"));
					cellDataValue.setTemplateId(rs.getString("TEMPLATE_ID"));
					cellDataValue.setCellType(rs.getString("CELL_TYPE"));
					//cellDataValue.setAreaType(rs.getString("AREA_TYPE"));
					list.add(cellDataValue);
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(CellDataValue.class);
	}

	//获得所有表内没有通过的校核
	public List<ValidationInfo> getValidationInfoByTemplateIdInTable(String annualId, String templateId, String dzzId) {
		//拼表名
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		String strSql = "select ID,TEMPLATE_ID,SOURCE_EXP,CELL_EXP,ACTUAL_EXP,VALIDATION_TYPE,VALIDATION_STATE,CELL_ID,DZZ_ID from "
				+ tableName
				+ " where TEMPLATE_ID = @templateId and DZZ_ID = @dzzId and VALIDATION_TYPE in ('1','2','3') and VALIDATION_STATE = '2'";
		Sql sql = Sqls.create(strSql);
		sql.params().set("templateId", templateId).set("dzzId", dzzId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<ValidationInfo> validationInfoList = new ArrayList<ValidationInfo>();
				while (rs.next()) {
					ValidationInfo validationInfo = new ValidationInfo();
					validationInfo.setId(rs.getString("ID"));
					validationInfo.setSourceExp(rs.getString("SOURCE_EXP"));
					validationInfo.setCellId(rs.getString("CELL_ID"));
					validationInfo.setActualExp(rs.getString("ACTUAL_EXP"));
					validationInfo.setCellExp(rs.getString("CELL_EXP"));
					validationInfo.setValidationType(rs.getString("VALIDATION_TYPE"));
					validationInfoList.add(validationInfo);
				}
				return validationInfoList;
			}
		});
		dao.execute(sql);
		return sql.getList(ValidationInfo.class);
	}

	// 获得该单位下的一套套表下的所有需要填报的报表
	public List<ViewUnitTemplate> getAllReportedFillTemplateDataIdByGroupId(String batchId, String groupId) {
		/*List<ViewUnitTemplate> list = dao.query(ViewUnitTemplate.class, Cnd
				.where("BATCH_ID", "=", batchId).and("GROUP_ID", "=", groupId)
				.asc("TEMPLATEORDERID"));*/
		Sql sql = Sqls.create("select t.batch_id as id,t.template_id as templateId, "
				+ "t.validation_state as validationState,t.examine_state as examineState, "
				+ "t.group_id as groupId,t.NAME as groupName,t.templateName as templateName, "
				+ "t.templateDescription as templateDescription,t.createdate as createDate, "
				+ "t.TEMPLATEORDERID as templateOrderId,t.ORDERID as orderId " + "from view_unit_template t "
				+ "left join rpt_batch b on t.batch_id=b.batch_id " + "where t.BATCH_ID=@batchId "
				/*+ "and t.GROUP_ID=@groupId "*/
				/*20161223 nutz版本太老 传参会把单引号拼成两个单引号*/
				/*+ "and b.report_state in ('1','2') "*/ + "order by TEMPLATEORDERID asc");
		sql.params().set("batchId", batchId);
		/*sql.params().set("groupId", groupId);*/
		/*
		 * 如果日后查看审核通过的报表，也需要此功能，
		 * 则下面report_state字符串里加'3';
		 * 如果查看审核退回的报表也需要，则字符串里加'100'
		 */
		//sql.params().set("report_state", "'1','2'"/* '3','100' */);
		sql.setCallback(Sqls.callback.records());
		dao.execute(sql);
		List<ViewUnitTemplate> list = sql.getList(ViewUnitTemplate.class);
		return list;
	}

	//获得该单位下的一套套表下的所有需要填报的报表
	public List<ViewUnitTemplate> getAllReportedFillTemplateDataId(String batchId) {
		Sql sql = Sqls.create("select t.batch_id as id,t.template_id as templateId, "
				+ "t.validation_state as validationState,t.examine_state as examineState, "
				+ "t.group_id as groupId,t.NAME as groupName,t.templateName as templateName, "
				+ "t.templateDescription as templateDescription,t.createdate as createDate, "
				+ "t.TEMPLATEORDERID as templateOrderId,t.ORDERID as orderId " + "from view_unit_template t "
				+ "left join rpt_batch b on t.batch_id=b.batch_id " + "where t.BATCH_ID=@batchId "
				/*20161223 nutz版本太老 传参会把单引号拼成两个单引号*/
				/*+ "and b.report_state in ('1','2') "*/ + "order by orderid asc,TEMPLATEORDERID asc");
		sql.params().set("batchId", batchId);
		/*
		 * 如果日后查看审核通过的报表，也需要此功能，
		 * 则下面report_state字符串里加'3';
		 * 如果查看审核退回的报表也需要，则字符串里加'100'
		 */
		//sql.params().set("report_state", "1\',\'2"/* '3','100' */);
		sql.setCallback(Sqls.callback.records());
		dao.execute(sql);
		List<ViewUnitTemplate> list = sql.getList(ViewUnitTemplate.class);
		return list;
	}

	/**
	 * 获得报表校核中批注校核和报表说明校核的条数
	 * @param annualId 年度id
	 * @param dzzId 党组织ID
	 */
	public String getValidationInfoByTemplateIdSubmitInformationCount(String annualId, String dzzId) {
		//拼表名
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		String strSql = "select count(1) from " + tableName
				+ " where DZZ_ID = @dzzId and VALIDATION_TYPE in ('5','6') and VALIDATION_STATE = '2'";
		Sql sql = Sqls.create(strSql);
		sql.params().set("tableName", tableName).set("dzzId", dzzId);
		sql.setCallback(Sqls.callback.integer());
		dao.execute(sql);
		return sql.getObject(Integer.class) + "";
	}

	/**
	 * 获得报表校核中该单位的所有批注校核和报表说明校核信息
	 * @param annualId 年度id
	 * @param dzzId 党组织ID
	 */
	public Map getValidationInfoByTemplateIdCommentAndSubmitInfo(String annualId, String dzzId, int pageNum,
			int pageSize, String templateId) {
		//拼表名
		String tableName = ReportConstant.VALIDATION_TABLE_PREFIX + annualId;
		String strSql = "select ID,TEMPLATE_ID,SOURCE_EXP,CELL_EXP,ACTUAL_EXP,VALIDATION_TYPE,VALIDATION_STATE,CELL_ID,DZZ_ID from "
				+ tableName
				+ " where DZZ_ID = @dzzId and VALIDATION_TYPE in ('5','6') and VALIDATION_STATE = '2' and TEMPLATE_ID = @templateId";

		String strCountSql = "select count(1) from " + tableName
				+ " where DZZ_ID = @dzzId and VALIDATION_TYPE in ('5','6') and VALIDATION_STATE = '2' and TEMPLATE_ID = @templateId";
		Sql sql = Sqls.create(strSql);
		sql.params().set("dzzId", dzzId).set("templateId", templateId);
		sql.setCallback(new SqlCallback() {
			@Override
			public Object invoke(Connection conn, ResultSet rs, Sql strSql) throws SQLException {
				List<ValidationInfo> validationInfoList = new ArrayList<ValidationInfo>();
				while (rs.next()) {
					ValidationInfo validationInfo = new ValidationInfo();
					validationInfo.setId(rs.getString("ID"));
					validationInfo.setSourceExp(rs.getString("SOURCE_EXP"));
					validationInfo.setCellId(rs.getString("CELL_ID"));
					validationInfo.setActualExp(rs.getString("ACTUAL_EXP"));
					validationInfo.setCellExp(rs.getString("CELL_EXP"));
					validationInfo.setValidationType(rs.getString("VALIDATION_TYPE"));
					validationInfo.setTemplateId(rs.getString("TEMPLATE_ID"));
					validationInfoList.add(validationInfo);
				}
				return validationInfoList;
			}
		});
		sql.setPager(dao.createPager(pageNum, pageSize));
		dao.execute(sql);

		Sql countSql = Sqls.create(strCountSql);
		countSql.params().set("dzzId", dzzId).set("templateId", templateId);
		countSql.setCallback(Sqls.callback.integer());
		dao.execute(countSql);

		Map<String, Object> result = new HashMap<String, Object>();
		result.put("Rows", sql.getList(ValidationInfo.class));
		result.put("Total", countSql.getObject(Integer.class));
		return result;
	}

	public int getCountByDzzId(String annualId, String dzzId) {
		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		String strSql = "select count(1) from " + tableName + " where DZZ_ID = @dzzId";
		Sql sql = Sqls.create(strSql);
		sql.params().set("dzzId", dzzId);
		sql.setCallback(Sqls.callback.integer());
		dao.execute(sql);
		return sql.getObject(Integer.class);
	}

	//根据传入TYPE，获取celldata(打印获取报表数据:非汇总)
	public List<CellDataValue> getAllCellDataValueByType(String templateId, String annualId, String dzzId,
			String cellType) {
		String tableName = ReportConstant.EXECUTIVE_TABLE_PREFIX + annualId;
		String sqlString = "select CELL_DATA_ID,CELL_VALUE,BATCH_ID,ABS_ROW_NUM,ABS_COL_NUM,REL_ROW_NUM,REL_COL_NUM,DZZ_ID,TEMPLATE_ID from "
				+ tableName + " where DZZ_ID = @dzzId and TEMPLATE_ID = @templateId and CELL_TYPE = @cellType";
		Sql sql = Sqls.create(sqlString);
		sql.params().set("dzzId", dzzId).set("templateId", templateId).set("cellType", cellType);
		sql.setCallback(new SqlCallback() {
			public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
				List<CellDataValue> list = new LinkedList<CellDataValue>();
				while (rs.next()) {
					CellDataValue cellDataValue = new CellDataValue();
					cellDataValue.setCellDataId(rs.getString("CELL_DATA_ID"));
					cellDataValue.setCellValue(rs.getString("CELL_VALUE"));
					cellDataValue.setBatchId(rs.getString("BATCH_ID"));
					cellDataValue.setAbsRowNum(rs.getString("ABS_ROW_NUM"));
					cellDataValue.setAbsColNum(rs.getString("ABS_COL_NUM"));
					cellDataValue.setRelRowNum(rs.getString("REL_ROW_NUM"));
					cellDataValue.setRelColNum(rs.getString("REL_COL_NUM"));
					cellDataValue.setDzzId(rs.getString("DZZ_ID"));
					cellDataValue.setTemplateId(rs.getString("TEMPLATE_ID"));
					list.add(cellDataValue);
				}
				return list;
			}
		});
		dao.execute(sql);
		return sql.getList(CellDataValue.class);
	}
}
